﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using ClassModel;
using System.Data.SqlClient;
using System.Data;

namespace ClassDAL
{
    public class t_class_merge_DAL
    {
        /// <summary>
        /// 插入合班信息
        /// </summary>
        /// <param name="merge"></param>
        /// <returns></returns>
        public static int InsertClassMerge(t_class_merge merge)
        {
            if (CheckClassMergeIsExists(merge)) return -1;

            string insertSQL = "insert into t_class_merge values(@taskid,@courseid,@classcount,@classids,@teacherids,@createtime);select @@identity;";

            SqlParameter[] insertParams = new SqlParameter[] 
            {
                new SqlParameter("@taskid",merge.taskid),
                new SqlParameter("@courseid",merge.courseid),
                new SqlParameter("@classcount",merge.classCount),
                new SqlParameter("@classids",merge.classIds),
                new SqlParameter("@teacherids",merge.teacherIds),
                new SqlParameter("@createtime",merge.createtime)
            };

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertSQL, insertParams));
        }

        /// <summary>
        /// 检查当前合班信息是否存在
        /// </summary>
        /// <param name="merge"></param>
        /// <returns></returns>
        private static bool CheckClassMergeIsExists(t_class_merge merge)
        { 
            string checkSQL = "select count(1) from t_class_merge where courseid = @courseid and classcount = @classcount and classids = @classids and teacherids = @tearcherids and taskid = @taskid;";

            SqlParameter[] checkParams = new SqlParameter[] 
            {
                new SqlParameter("@courseid",merge.courseid),
                new SqlParameter("@classcount",merge.classCount),
                new SqlParameter("@taskid",merge.taskid),
                new SqlParameter("@classids",merge.classIds),
                new SqlParameter("@tearcherids",merge.teacherIds)
            };

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, checkSQL, checkParams)) > 0;
        }


        /// <summary>
        /// 根据任务id获取合班信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <returns></returns>
        public static List<t_class_merge> GetMergeListWithTaskId(int taskid)
        {

            //StringBuilder selectBuilder = new StringBuilder();
            //selectBuilder.AppendLine("select tcm.* ,tcc.coursename,tctc.tearcherid,tct.tearcher from ");
            //selectBuilder.AppendLine("(select id,taskid,courseid,classcount,classids,createtime from t_class_merge where taskid = @taskid) tcm");
            //selectBuilder.AppendLine("left join t_class_course tcc on tcm.courseid = tcc.id");
            //selectBuilder.AppendLine("left join t_class_tearcher_course tctc on tcc.id = tctc.courseid");
            //selectBuilder.AppendLine("left join t_class_tearchers tct on tctc.tearcherid = tct.id");

            //string selectSQL = selectBuilder.ToString();

            string selectSQL = @"select mergeT.*,courseT.coursename from 
                                (select id,taskid,courseid,classcount,classids,teacherids,createtime from t_class_merge where taskid = @taskid) mergeT
                            left join t_class_course courseT on mergeT.courseid = courseT.id";

            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@taskid",
                Value = taskid
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);
            if (ds == null || ds.Tables.Count == 0) return null;

            List<t_class_merge> mergeList = new List<t_class_merge>();
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                t_class_merge merge = new t_class_merge();
                merge.id = Convert.ToInt32(row["id"]);
                merge.taskid = Convert.ToInt32(row["taskid"]);
                merge.courseid = Convert.ToInt32(row["courseid"]);
                merge.classCount = Convert.ToInt32(row["classcount"]);
                merge.classIds = row["classids"].ToString();
                merge.teacherIds = row["teacherids"].ToString();
                merge.createtime = Convert.ToDateTime(row["createtime"]);
                merge.courseName = row["courseName"].ToString();

                mergeList.Add(merge);
            }

            return mergeList;
        }

        /// <summary>
        /// 根据课程id获取合班信息
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public static t_class_merge GetMergeInfoByCourseId(int courseid)
        {
            string selectSQL = "select id,taskid,courseid,classcount,classids,teacherids,createtime from t_class_merge where courseid = @courseid;";
            SqlParameter selectParams = new SqlParameter()
            {
                ParameterName = "@courseid",
                Value = courseid
            };

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParams);
            if (!reader.Read()) return null;

            t_class_merge merge = new t_class_merge();

            merge.id = Convert.ToInt32(reader["id"]);
            merge.taskid = Convert.ToInt32(reader["taskid"]);
            merge.courseid = Convert.ToInt32(reader["courseid"]);
            merge.classCount = Convert.ToInt32(reader["classcount"]);
            merge.classIds = reader["classids"].ToString();
            merge.teacherIds = reader["teacherids"].ToString();
            merge.createtime = Convert.ToDateTime(reader["createtime"]);


            reader.Close();

            return merge;
        }


        /// <summary>
        /// 删除某条合班信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int DeleteMergeInfo(int taskid, int id)
        {
            string deleteSQL = "delete from t_class_merge where id = @id and taskid = @taskid;";
            SqlParameter[] deleteParams = new SqlParameter[] 
            {
                new SqlParameter("@id",id),
                new SqlParameter("@taskid",taskid)
            };

            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), CommandType.Text, deleteSQL, deleteParams);
        }
    }
}
